package com.wondersgroup.fjzlpt.yyzyzx.dao.impl.yyzycx;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.page.PageQuerySQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;
import com.quick.framework.boot.repository.sql.jdbcTemplate.rs.ResultHandlerConstant;
import com.quick.framework.util.ExcelOrWordUtils;
import com.quick.framework.util.LoggerUtil;
import com.quick.framework.util.QuickPrincipalUtils;
import com.wondersgroup.fjzlpt.yyzyzx.dao.inter.yyzycx.CwxxDAO;
import com.wondersgroup.fjzlpt.yyzyzx.util.ControllerUtils;

import jxl.CellView;
import jxl.Workbook;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import jxl.write.Alignment;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

/**
 * 床位信息数据访问实现层
 * <p>
 * <p>
 * Created by sanma on 2017/8/7.
 */
@Repository
public class CwxxDAOImpl  extends BaseJTDAOImpl implements CwxxDAO{

	@Override
	@Autowired
	@Qualifier("TB_YYZY_CWXX")
	public void setTb(TableBean tb) {
		this.tb = tb;
	}

	/**
     *  分页查询床位信息（查询视图）
     * @param map
     * @return
     */
    @Override
    public JSONObject pageQueryCwxxAll(Map map) {
		ControllerUtils.pageFormat(map);
    	JSONObject sqlObj = this.getQueryPageSqlOnView(map);
		
		List list = sqlObj.getJSONArray("sqlParams");
        
        PageQuerySQLBean sb = checkAndCreatePageQuerySQLBean(map, sqlObj.getString("sql"), " order by YYJBDM DESC ", list.toArray());
        return super.queryPage(sb, ResultHandlerConstant.JSONARRAY);
    }
    
    /**
     * 查询所有床位信息（查询视图）
     * @param map
     * @return
     */
    public JSONArray queryAllOnView(Map map){
    	JSONObject sqlObj = this.getQueryPageSqlOnView(map);
		
		List list = sqlObj.getJSONArray("sqlParams");
		
		SQLBean bean = new SQLBean(sqlObj.getString("sql") + " order by YYJBDM DESC ", list.toArray());
    	return super.queryList(bean);
    }
    
    /**
	 * 分页语句拼接（查询视图）
	 * @param map
	 * @return
	 */
	private JSONObject getQueryPageSqlOnView(Map<String,String> map){
		JSONObject result = new JSONObject();
		
		//select CWXXBH , YLJGMC , XZQHMC , YYJBMC , KSMC_1 KSMC , BQMC , SJKFCWS , DQKCS , DQKYYCWS , DAY3KCS STNYJKCS ,
        //        DAY3KYYCWS STNKYYCWS , WRRK1KCS YZNYJKCS , WEEK1KYYCWS YZNKYYCWS , DDCWS , CWSYL  from V_ZYZX_CWXX;
		StringBuilder sbud = new StringBuilder("select CWXXBH , YLJGMC, YLJGDM ,XZQHDM, XZQHMC ,YYJBDM,YYJBMC , KSDM_1 YJKSDM, KSMC_1 KSMC ,BQDM, ")
				.append(" BQMC,SJKFCWS , DQKCS , DQKYYCWS , DAY3KCS STNYJKCS , DAY3KYYCWS STNKYYCWS , WEEK1KCS YZNYJKCS ,")
                .append("  WEEK1KYYCWS YZNKYYCWS ,DDCWS , CWSYL,YYYCWS  from V_ZYZX_CWXX WHERE 1 = 1 \n");
        List list = new ArrayList();
        String value = (String)map.get("XZQHDM");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and XZQHDM LIKE ? \n");
            list.add(value + "%");
        }
        value = (String)map.get("YYDJ");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and YYJBDM = ? \n");
            list.add(value);
        }
        value = (String)map.get("YLJGMC");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and YLJGMC LIKE ? \n");
            list.add("%" + value + "%");
        }
        value = (String)map.get("KSMC");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and KSMC_1 LIKE ? \n");
            list.add("%" + value + "%");
        }
        value = (String)map.get("BQMC");
        if(!StringUtils.isEmpty(value)){
            sbud.append(" and BQMC LIKE ? \n");
            list.add("%" + value + "%");
        }
		value = (String)map.get("YLJGDM");
		if(!StringUtils.isEmpty(value)){
			sbud.append(" and YLJGDM = ? \n");
			list.add(value);
		}
		
		
		result.put("sql", sbud.toString());
		result.put("sqlParams", list);
		return result;
	} 
    
    

    /**
     * 分页查询床位信息（查询床位信息表）
     * @param map
     * @return
     */
	@Override
	public JSONObject queryPage(Map map) {
		ControllerUtils.pageFormat(map);
		JSONObject sqlObj = this.getQueryPageSql(map);
		
		List sqlParams = sqlObj.getJSONArray("sqlParams");
		PageQuerySQLBean pageBean = super.checkAndCreatePageQuerySQLBean(map, sqlObj.getString("sql"), null, sqlParams.toArray());
		
		return super.queryPage(pageBean, ResultHandlerConstant.JSONARRAY);
	}
	
	/**
     * 查询所有床位信息（查询床位信息表）
     * @param map
     * @return
     */
	@Override
	public JSONArray queryAll(Map map) {
		JSONObject sqlObj = this.getQueryPageSql(map);
		
		List sqlParams = sqlObj.getJSONArray("sqlParams");
		
		SQLBean bean = new SQLBean(sqlObj.getString("sql"), sqlParams.toArray());
		return super.queryList(bean);
	}
	
	/**
	 * 分页语句拼接
	 * @param map
	 * @return
	 */
	private JSONObject getQueryPageSql(Map<String,String> map){
		JSONObject result = new JSONObject();
		
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select * from ").append(this.tb.getTableName()).append(" where 1=1 \n");
		
		List sqlParams = new ArrayList();
		
		//行政区划代码
		String value = map.get("XZQHDM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and XZQHDM LIKE ? ");
			sqlParams.add(value + "%");
		}
		//医疗机构代码
		value = map.get("YLJGDM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and YLJGDM = ? ");
			sqlParams.add(value);
		}
		//医疗机构名称
		value = map.get("YLJGMC");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and YLJGMC like ? ");
			sqlParams.add("%" + value + "%");
		}
		//病区名称
		value = map.get("BQMC");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and BQMC like ? ");
			sqlParams.add("%" + value + "%");
		}
		//科室名称
		value = map.get("KSMC");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and YJKSMC like ? ");
			sqlParams.add("%" + value + "%");
		}
		
		
		result.put("sql", sqlBuf.toString());
		result.put("sqlParams", sqlParams);
		return result;
	}

	/**
	 * 根据机构信息查询所有的一级科室
	 * @param map
	 * @return
	 */
	public JSONArray queryYjksByJgxx(Map<String,String> map){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.* from TB_ZD_KSXX a left join TB_ZD_YLJGXX b on a.PTJGID=b.PTJGID where a.JB='1' ");

		List<String> sqlParams = new ArrayList<String>();
		String value = map.get("PTJGID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.PTJGID = ? ");
			sqlParams.add(value);
		}
		value = map.get("YLJGDM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and b.YLJGDM = ? ");
			sqlParams.add(value);
		}
		sqlBuf.append("order by a.KEYWORD ");
		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		return super.query(bean, ResultHandlerConstant.JSONARRAY);

	}

	/**
	 * 根据机构信息查询所有的病区信息
	 * @param map
	 * @return
	 */
	public JSONArray queryBqxxByJgxx(Map<String,String> map){
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.* from tb_zd_bqxx a left join TB_ZD_YLJGXX b on a.PTJGID=b.PTJGID where 1=1 \n");

		List<String> sqlParams = new ArrayList<String>();

		String value = map.get("PTJGID");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.PTJGID = ? ");
			sqlParams.add(value);
		}
		value = map.get("YLJGDM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and b.YLJGDM = ? ");
			sqlParams.add(value);
		}
		//科室代码
		value = map.get("KSDM");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.KSDM = ? ");
			sqlParams.add(value);
		}

		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		return super.query(bean, ResultHandlerConstant.JSONARRAY);
	}

	/**
	 * 根据行政代码查询名称
	 * @param xzqhdm
	 * @return
	 */
	public String queryXzqhmcByXzqhdm(String xzqhdm){
		SQLBean bean = new SQLBean("select XZQHMC from TB_ZY_XZQH where XZQHDM = ?", xzqhdm);
		JSONObject xzqhObj = super.queryOne(bean);
		return xzqhObj.getString("XZQHMC");
	}

	/**
	 * 导入值域
	 *
	 * @param arr              需要导出的数据
	 * @param titleField       数据key值，导出结果也按此排序
	 * @param downLoadFileName 导出文件的名字
	 * @param response
	 */
	public void downExcelModelZy(JSONArray arr, List<String> titleField, String downLoadFileName, HttpServletRequest request, HttpServletResponse response){
		WritableWorkbook wbook = null;
		OutputStream out = null;
		try {
			ExcelOrWordUtils.setOutFileName(downLoadFileName, request, response);
			out = response.getOutputStream();
			wbook = Workbook.createWorkbook(out);
			// 文本样式
			WritableCellFormat wcfF = new WritableCellFormat(NumberFormats.TEXT); // 定义一个单元格样式
			wcfF.setAlignment(Alignment.CENTRE); //设置X轴对其方式
			wcfF.setVerticalAlignment(VerticalAlignment.CENTRE); //设置Y轴对其方式
			CellView cv = new CellView(); // 定义一个列显示样式
			cv.setFormat(wcfF);// 把定义的单元格格式初始化进去
			cv.setSize(20 * 265);// 设置列宽度（不设置的话是0，不会显示） //cv.setAutosize(true); //设置自动大小


			WritableSheet sheet = wbook.createSheet("Sheet1", 0);
			sheet.getSettings().setVerticalFreeze(1);  // 冻结行
			for (int i = 0; i < titleField.size(); i++) {
				sheet.setColumnView(i, cv);// 将改列格式该文 文本格式
			}

			String sql = "select a.KSDM,a.KSMC,b.BQMC from TB_ZD_KSXX a left join tb_ZD_BQXX b on a.PTJGID=b.PTJGID and a.KSDM=b.KSDM where a.JB='1' and a.ptjgid= ? order by a.KSDM";
			SQLBean bean = new SQLBean(sql, QuickPrincipalUtils.getPtjgID(request));
			JSONArray data = super.queryList(bean);
			arr.addAll(data);
			JSONObject firstObj = arr.getJSONObject(1);
			//数据载入
			int Y = 1;
			String KSDM = firstObj.getString("KSDM");
			for (int i = 0; i < arr.size(); i++) {
				JSONObject sigle = arr.getJSONObject(i);
				for (int j = 0; j < titleField.size(); j++) {
					sheet.addCell(new Label(j, i, sigle.getString(titleField.get(j))));
				}
				if(sigle.getString("KSDM") != null && !sigle.getString("KSDM").equals(KSDM) ){
					sheet.mergeCells(0, Y, 0, i-1);//合并单元格
					Y = i;
				}else if(i==arr.size()-1){
					sheet.mergeCells(0, Y, 0, i);//合并单元格
				}
				if(sigle.getString("KSDM") != null){
					KSDM = sigle.getString("KSDM");
				}
			}
			// 写入表格
			wbook.write();
		} catch (RowsExceededException e) {
			LoggerUtil.error(e.getMessage());
		} catch (WriteException e) {
			LoggerUtil.error(e.getMessage());
		} catch (IOException e) {
			LoggerUtil.error(e.getMessage());
		} finally {
			if (wbook != null) {
				try {
					wbook.close();
				} catch (Exception e) {
					LoggerUtil.error(e.getMessage());
				}
			}
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					LoggerUtil.error(e.getMessage());
				}
			}
		}
	}


}
